const XLSX = require('xlsx');
const mysql = require('mysql2/promise');

// 数据库连接
const db = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '1234',
  database: 'word_memory'
});

// 读取 Excel 文件
const workbook = XLSX.readFile('./Antonyms_Result.xlsx');
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet); // 默认读取字段行

async function main() {
  for (const row of data) {
    const { Word, 'Antonyms JSON': AntonymsJSON } = row;
    if (!Word || !AntonymsJSON) continue;

    try {
      // 查找单词 ID
      const [wordRows] = await db.query('SELECT id FROM words WHERE word = ?', [Word]);
      if (wordRows.length === 0) {
        console.warn(`❗️单词未找到，跳过：${Word}`);
        continue;
      }

      const wordId = wordRows[0].id;

      // 解析 JSON
      let parsedAntonyms;
      try {
        parsedAntonyms = JSON.parse(AntonymsJSON);
      } catch (err) {
        console.error(`❌ JSON 解析失败：${Word} - ${err.message}`);
        continue;
      }

      // 插入数据库
      await db.query(
        'INSERT INTO word_antonyms (word_id, antonym) VALUES (?, ?)',
        [wordId, JSON.stringify(parsedAntonyms)]
      );

      console.log(`✅ 已插入：${Word}`);
    } catch (err) {
      console.error(`❌ 插入失败：${Word} - ${err.message}`);
    }
  }

  console.log('🎉 所有反义词导入完成');
}

main();
